package net.teamclerks.kain.irc.bot.utils.tags;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/*
 * TagHandler.java
 *
 * Copyright (C) 2007 Mike 'kain' Smith
 * 
 * This is part of HelperBot
 * 
 * HelperBot is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 * 
 * HelperBot is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 */

public final class TagHandler {

  // The URL of the database server
  private String cString;
  
  protected TagHandler()
  {
  }
  
  public TagHandler(String cString)
  {
    this.cString = cString;
  }
  
  public String getRandomTag() throws SQLException
  {
    ArrayList<String> output = new ArrayList<String>();
    // Sentinel 
    Connection con = getConnection();
    try
    {
      // This will seed the randomization so that tags that are mapped
      // more will show up more often in the random than a tag that only
      // shows up once.
      String sql = "SELECT tags.tag FROM tagmapping JOIN tags ON tagmapping.tag_id = tags.id ORDER BY RAND() LIMIT 1;";
      
      PreparedStatement stmt = con.prepareStatement(sql);
      ResultSet rs = stmt.executeQuery();
      while(rs.next())
      {
        output.add(rs.getString("tag"));
      }
    }
    finally
    {
      // Yeah...
      con.close();
    }
    
    return output.get(0);
  }
  
  public ArrayList<String> getUrlsForTags(ArrayList<String> tags, int count) throws SQLException
  {
    ArrayList<String> all = getUrlsForTags(tags);
    ArrayList<String> temp = new ArrayList<String>();
    for(int i=0; i<count; i++)
    {
      try
      {
        temp.add(all.get(i));
      }
      catch (IndexOutOfBoundsException ioobe)
      {
        // Means there were fewer than count, so just fall out.
        break;
      }
    }
    return temp;
  }

  /**
   * Pass it a tag, it'll give you the urls related to it.
   * @param tag
   */ 
   public ArrayList<String> getUrlsForTags(ArrayList<String> tags) throws SQLException
   {
     // Sentinel
     Connection con = getConnection();
     ArrayList<String> urls = new ArrayList<String>();
     try
     {
       String sql =        "select url from tags " +
                           "join tagmapping as t on tags.id = t.tag_id " +
                           "join urls on urls.id = t.url_id ";
       String where = "where ";
       String groupBy = "group by url having (count(url) = ?) " +
                        "order by date desc;";
       
       for(String s: tags)
       {
         s = s + ""; // Garbage to get rid of a warning.
         if(where.length() > 6)
         {
           // Add an 'or' op because the where has some args already.
           where += "or ";
         }
         where += "tag = ? ";
       }
       // Combine the sql, where, and groupBy queries to make the full sql.
       sql += where;
       sql += groupBy;
       
       PreparedStatement stmt = con.prepareStatement(sql);
       int i=1;
       for(i=1; i<tags.size()+1; i++)
       {
         stmt.setString(i,tags.get(i-1).replaceAll("[^A-Za-z0-9]",""));
       }
       stmt.setInt(i, tags.size());
       ResultSet rs = stmt.executeQuery();
       while(rs.next())
       {
         urls.add(rs.getString("url"));
       }
     }
     finally
     {
       // Yeah...
       con.close();
     }
     
     return urls;
   }


  /**
   * Returns an ArrayList<String> of tags for the given URL.
   * @param url
   * @return tags
   * @throws SQLException
   */  
   public ArrayList<String> getTagsForUrl(String url) throws SQLException
   {
     // Sentinel 
     Connection con = getConnection();
     ArrayList<String> output = new ArrayList<String>();
     
     try
     {
       String sql = "select tag from tags " +
                    "join tagmapping as t on tags.id = t.tag_id " +
                    "join urls on urls.id = t.url_id " +
                    "where url = ?";
       
       PreparedStatement stmt = con.prepareStatement(sql);
       stmt.setString(1,url);
       ResultSet rs = stmt.executeQuery();
       while(rs.next())
       {
         output.add(rs.getString("tag"));
       }
     }
     finally
     {
       // Yeah...
       con.close();
     }
     
     return output;
   }
  
  /**
   * Helper method for counting urls for one tag search.
   * @param tag
   * @return
   * @throws SQLException
   */
  public int getCountForTag(String tag) throws SQLException
  {
    // Sentinel
    Connection con = getConnection();
    int output = 0;
    
    try
    {
      String sql = "select count(t.tag_id) as count from tagmapping as t join tags on t.tag_id = tags.id where tag = ?";
      // OLD SCHEMA
      //String sql = "select count(*) as count from id_search_table where ? in(tag1,tag2,tag3,tag4,tag5,tag6,tag7,tag8,tag9,tag10)";
      PreparedStatement stmt = con.prepareStatement(sql);
      stmt.setString(1,tag);
      ResultSet rs = stmt.executeQuery();
      rs.next();
      output = rs.getInt("count");
    }
    finally
    {
      // Yeah...
      con.close();
    }
    
    return output;
  }
  
  /**
   * Helper method for counting urls by a number of tags on a search.
   * @param tags
   * @return number of urls
   * @throws SQLException
   */
  public int getCountForTags(ArrayList<String> tags) throws SQLException
  {
    // Sentinel
    Connection con = getConnection();
    int output = 0;
    
    try
    {
      String sql =        "select url from tags " +
                          "join tagmapping as t on tags.id = t.tag_id " +
                          "join urls on urls.id = t.url_id ";
      String where = "where ";
      String groupBy = "group by url having (count(url) > ?);";
      
      for(String s: tags)
      {
        s = s + ""; // Garbage to get rid of a warning.
        if(where.length() > 6)
        {
          // Add an 'or' op because the where has some args already.
          where += "or ";
        }
        where += "tag = ? ";
      }
      // Combine the sql, where, and groupBy queries to make the full sql.
      sql += where;
      sql += groupBy;
      
      PreparedStatement stmt = con.prepareStatement(sql);
      int i=1;
      for(i=1; i<tags.size()+1; i++)
      {
        stmt.setString(i,tags.get(i-1).replaceAll("[^A-Za-z0-9]",""));
      }
      stmt.setInt(i, tags.size()-1);
      ResultSet rs = stmt.executeQuery();
      rs.next();
      output = rs.getFetchSize();
    }
    finally
    {
      // Yeah...
      con.close();
    }
    
    return output;
    // there HAS to be a better way to do this.
  }
  
  /**
   * Adds a list of tags to a given url, if the url doesn't exist, it's created.
   * @param url
   * @param tags
   */
  public void addTagsToUrl(String url, ArrayList<String> tags, String tagger, String login, String hostname) throws SQLException
  {
    this.updateSearchTableEntry(url, tags, hostname);
    this.updateAliases(tagger, login, hostname);
  }
  
  /**
   * Adds a single tag to a url
   * @param url
   * @param tag
   * @throws SQLException
   */
  public void addTagToUrl(String url, String tag, String tagger) throws SQLException
  {    
    ArrayList<String> tags = new ArrayList<String>();
    tags.add(tag);
    this.updateSearchTableEntry(url, tags, tagger);
  }
  
  /**
   * Adds tags to the database directly without any mapping.
   * @param tags
   * @return list of insert ids
   * @throws SQLException
   */
  public ArrayList<Integer> addTags(List<String> tags) throws SQLException
  {
    // Sentinel 
    Connection con = getConnection();
    
    ArrayList<Integer> tagIds = new ArrayList<Integer>();
    
    try
    {
      for(String s: tags)
      {
        String tagInsert = "INSERT IGNORE INTO tags(tag) VALUES(?)";
        PreparedStatement stmt = con.prepareStatement(tagInsert);
        stmt.setString(1, s); // because sql args start at 1
        stmt.executeUpdate();
        // Now that tag is for sure in the db
        String tagQuery = "SELECT id FROM tags WHERE tag = ?";
        stmt = con.prepareStatement(tagQuery);
        stmt.setString(1, s); // because sql args start at 1
        ResultSet rs = stmt.executeQuery();
        rs.next(); // Sets up the initial first row, if there is one.
        tagIds.add(rs.getInt("id"));
      }
    }
    finally
    {
      con.close();
    }
    
    return tagIds;
  }
  
  /******************
   *    Privates    *
   *****************/
  
  private void updateAliases(String sender, String login, String hostname) throws SQLException
  {
    // Sentinel 
    Connection con = getConnection();

    try
    {
      String insert = "INSERT IGNORE INTO aliases(sender, login, hostname) VALUES(?,?,?)";
      PreparedStatement stmt = con.prepareStatement(insert);
      stmt = con.prepareStatement(insert);
      stmt.setString(1,sender);
      stmt.setString(2, login);
      stmt.setString(3, hostname);
      stmt.executeUpdate();
    }
    finally
    {
      // Yeah...
      con.close();
    }
  }
  
  /**
   * Tries to setup a connection
   * @throws SQLException
   */
  private Connection getConnection() throws SQLException
  {
    try
    {
      Class.forName("com.mysql.jdbc.Driver");
      return DriverManager.getConnection(cString,"helper","tagging");
    }
    catch(ClassNotFoundException e)
    {
      // NO IDEA
      e.printStackTrace();
    }
    catch(SQLException e)
    {
      // !?
      e.printStackTrace();
    }
    return null;
  }
  
  /**
   * Updates the tags for a url. Will create one if there is not the given url in the database.
   * @param url
   * @param tags
   * @param sender
   * @throws SQLException
   */
    private void updateSearchTableEntry(String url, ArrayList<String> tags, String hostname) throws SQLException
    {
      // Sentinel 
      Connection con = getConnection();
      
      ArrayList<Integer> tagIds = new ArrayList<Integer>();
      
      try
      {
        tagIds = this.addTags(tags);
        
        String sql = "INSERT IGNORE INTO urls(url) VALUES(?)";
        PreparedStatement stmt = con.prepareStatement(sql);
        stmt.setString(1, url);
        stmt.executeUpdate();
        
        String urlQuery = "SELECT id FROM urls WHERE url = ?";
        stmt = con.prepareStatement(urlQuery);
        stmt.setString(1, url); // because sql args start at 1
        ResultSet rs = stmt.executeQuery();
        rs.next(); // Sets up the initial first row
        
        int urlID = rs.getInt("id");
        
        // Got all our ids, time to tag
        for(Integer i: tagIds)
        {
          String insert = "INSERT IGNORE INTO tagmapping(tag_id, url_id, hostname) VALUES(?,?,?)";
          stmt = con.prepareStatement(insert);
          stmt.setInt(1,i);
          stmt.setInt(2, urlID);
          stmt.setString(3, hostname);
          stmt.executeUpdate();
        }
      }
      finally
      {
        // Yeah...
        con.close();
      }
    }
}
